Insert records from URL (JSON format) to PostgreSQL databaseΒΆ
Insert records from URL (JSON format) to PostgreSQL database
import urllib.request, json
"""
Database preparation:
CREATE TABLE IF NOT EXISTS users
(
user_id INT NOT NULL,
company_id INT,
name VARCHAR(64),
username VARCHAR(32),
email VARCHAR(128),
phone VARCHAR(32),
website VARCHAR(128),
CONSTRAINT user_id_pk PRIMARY KEY(user_id),
CONSTRAINT company_id_fk FOREIGN KEY (company_id)
REFERENCES companies (company_id)
ON DELETE SET NULL
ON UPDATE NO ACTION
);
CREATE SEQUENCE companies_sequence
start 1
increment 1;
CREATE TABLE IF NOT EXISTS companies
(
company_id INT NOT NULL,
address_id INT,
name VARCHAR(64),
catchPhrase VARCHAR(64),
bs VARCHAR(64),
CONSTRAINT company_id_pk PRIMARY KEY(company_id),
CONSTRAINT address_id_fk FOREIGN KEY (address_id)
REFERENCES addresses (address_id)
ON DELETE SET NULL
ON UPDATE NO ACTION
);
CREATE SEQUENCE addresses_sequence
start 1
increment 1;
CREATE TABLE IF NOT EXISTS addresses
(
address_id INT NOT NULL,
street VARCHAR(64),
suite VARCHAR(16),
city VARCHAR(64),
zipcode VARCHAR(12),
lat FLOAT,
lng FLOAT,
CONSTRAINT address_id_pk PRIMARY KEY(address_id)
);
"""
# url = "http://maps.googleapis.com/maps/api/geocode/json?address=google"
url = "https://jsonplaceholder.typicode.com/users"
with urllib.request.urlopen(url) as response:
data_list = json.load(response)
use_sequences = True # False
address_id = 1
company_id = 1
for rec_dict in data_list:
user_flds = ['user_id', 'company_id']
user_vals = []
for item_name, item_value in rec_dict.items():
if item_name == 'address':
addr_flds = ['address_id']
if use_sequences:
addr_vals = ['NEXTVAL(addresses_sequence)']
else:
addr_vals = [str(address_id)]
for addr_fld, addr_val in item_value.items():
if addr_fld == 'geo':
addr_flds.extend(['lat', 'lng'])
addr_vals.extend([addr_val['lat'], addr_val['lng']])
else:
addr_flds.append(addr_fld)
addr_vals.append("'" + addr_val + "'")
elif item_name == 'company':
comp_flds = ['company_id, address_id']
if use_sequences:
comp_vals = ['NEXTVAL(companies_sequence)']
comp_vals.append(str(address_id))
else:
comp_vals = [str(company_id), str(address_id)]
for comp_fld, comp_val in item_value.items():
comp_flds.append(comp_fld)
comp_vals.append("'" + comp_val + "'")
else:
if item_name == 'id':
user_id = item_value
user_vals.extend([str(user_id), str(company_id)])
else:
user_flds.append(item_name)
user_vals.append("'" + str(item_value) + "'")
if addr_flds:
print('INSERT INTO addresses({}) VALUES ({}) RETURNING address_id INTO address_id;'.format(",".join(addr_flds), ",".join(addr_vals)))
if comp_flds:
print('INSERT INTO companies({}) VALUES ({}) RETURNING company_id INTO company_id;'.format(",".join(comp_flds), ",".join(comp_vals)))
if user_flds:
print('INSERT INTO users({}) VALUES ({}) RETURNING user_id INTO user_id;'.format(",".join(user_flds), ",".join(user_vals)))
# print('UPDATE users SET address_id = {} WHERE user_id = {};'.format(address_id, user_id))
print('UPDATE users SET company_id = {} WHERE user_id = {};'.format(company_id, user_id))
address_id += 1
company_id += 1
print("="*40)
Output:
INSERT INTO addresses(address_id,street,suite,city,zipcode,lat,lng) VALUES (NEXTVAL(addresses_sequence),'Kulas Light','Apt. 556','Gwenborough','92998-3874',-37.3159,81.1496) RETURNING address_id INTO address_id;
INSERT INTO companies(company_id, address_id,name,catchPhrase,bs) VALUES (NEXTVAL(companies_sequence),1,'Romaguera-Crona','Multi-layered client-server neural-net','harness real-time e-markets') RETURNING company_id INTO company_id;
INSERT INTO users(user_id,company_id,name,username,email,phone,website) VALUES (1,1,'Leanne Graham','Bret','Sincere@april.biz','1-770-736-8031 x56442','hildegard.org') RETURNING user_id INTO user_id;
UPDATE users SET company_id = 1 WHERE user_id = 1;
========================================
INSERT INTO addresses(address_id,street,suite,city,zipcode,lat,lng) VALUES (NEXTVAL(addresses_sequence),'Victor Plains','Suite 879','Wisokyburgh','90566-7771',-43.9509,-34.4618) RETURNING address_id INTO address_id;
INSERT INTO companies(company_id, address_id,name,catchPhrase,bs) VALUES (NEXTVAL(companies_sequence),2,'Deckow-Crist','Proactive didactic contingency','synergize scalable supply-chains') RETURNING company_id INTO company_id;
INSERT INTO users(user_id,company_id,name,username,email,phone,website) VALUES (2,2,'Ervin Howell','Antonette','Shanna@melissa.tv','010-692-6593 x09125','anastasia.net') RETURNING user_id INTO user_id;
UPDATE users SET company_id = 2 WHERE user_id = 2;
========================================
INSERT INTO addresses(address_id,street,suite,city,zipcode,lat,lng) VALUES (NEXTVAL(addresses_sequence),'Douglas Extension','Suite 847','McKenziehaven','59590-4157',-68.6102,-47.0653) RETURNING address_id INTO address_id;
INSERT INTO companies(company_id, address_id,name,catchPhrase,bs) VALUES (NEXTVAL(companies_sequence),3,'Romaguera-Jacobson','Face to face bifurcated interface','e-enable strategic applications') RETURNING company_id INTO company_id;
INSERT INTO users(user_id,company_id,name,username,email,phone,website) VALUES (3,3,'Clementine Bauch','Samantha','Nathan@yesenia.net','1-463-123-4447','ramiro.info') RETURNING user_id INTO user_id;
UPDATE users SET company_id = 3 WHERE user_id = 3;
========================================
INSERT INTO addresses(address_id,street,suite,city,zipcode,lat,lng) VALUES (NEXTVAL(addresses_sequence),'Hoeger Mall','Apt. 692','South Elvis','53919-4257',29.4572,-164.2990) RETURNING address_id INTO address_id;
INSERT INTO companies(company_id, address_id,name,catchPhrase,bs) VALUES (NEXTVAL(companies_sequence),4,'Robel-Corkery','Multi-tiered zero tolerance productivity','transition cutting-edge web services') RETURNING company_id INTO company_id;
INSERT INTO users(user_id,company_id,name,username,email,phone,website) VALUES (4,4,'Patricia Lebsack','Karianne','Julianne.OConner@kory.org','493-170-9623 x156','kale.biz') RETURNING user_id INTO user_id;
UPDATE users SET company_id = 4 WHERE user_id = 4;
========================================
INSERT INTO addresses(address_id,street,suite,city,zipcode,lat,lng) VALUES (NEXTVAL(addresses_sequence),'Skiles Walks','Suite 351','Roscoeview','33263',-31.8129,62.5342) RETURNING address_id INTO address_id;
INSERT INTO companies(company_id, address_id,name,catchPhrase,bs) VALUES (NEXTVAL(companies_sequence),5,'Keebler LLC','User-centric fault-tolerant solution','revolutionize end-to-end systems') RETURNING company_id INTO company_id;
INSERT INTO users(user_id,company_id,name,username,email,phone,website) VALUES (5,5,'Chelsey Dietrich','Kamren','Lucio_Hettinger@annie.ca','(254)954-1289','demarco.info') RETURNING user_id INTO user_id;
UPDATE users SET company_id = 5 WHERE user_id = 5;
========================================
INSERT INTO addresses(address_id,street,suite,city,zipcode,lat,lng) VALUES (NEXTVAL(addresses_sequence),'Norberto Crossing','Apt. 950','South Christy','23505-1337',-71.4197,71.7478) RETURNING address_id INTO address_id;
INSERT INTO companies(company_id, address_id,name,catchPhrase,bs) VALUES (NEXTVAL(companies_sequence),6,'Considine-Lockman','Synchronised bottom-line interface','e-enable innovative applications') RETURNING company_id INTO company_id;
INSERT INTO users(user_id,company_id,name,username,email,phone,website) VALUES (6,6,'Mrs. Dennis Schulist','Leopoldo_Corkery','Karley_Dach@jasper.info','1-477-935-8478 x6430','ola.org') RETURNING user_id INTO user_id;
UPDATE users SET company_id = 6 WHERE user_id = 6;
========================================
INSERT INTO addresses(address_id,street,suite,city,zipcode,lat,lng) VALUES (NEXTVAL(addresses_sequence),'Rex Trail','Suite 280','Howemouth','58804-1099',24.8918,21.8984) RETURNING address_id INTO address_id;
INSERT INTO companies(company_id, address_id,name,catchPhrase,bs) VALUES (NEXTVAL(companies_sequence),7,'Johns Group','Configurable multimedia task-force','generate enterprise e-tailers') RETURNING company_id INTO company_id;
INSERT INTO users(user_id,company_id,name,username,email,phone,website) VALUES (7,7,'Kurtis Weissnat','Elwyn.Skiles','Telly.Hoeger@billy.biz','210.067.6132','elvis.io') RETURNING user_id INTO user_id;
UPDATE users SET company_id = 7 WHERE user_id = 7;
========================================
INSERT INTO addresses(address_id,street,suite,city,zipcode,lat,lng) VALUES (NEXTVAL(addresses_sequence),'Ellsworth Summit','Suite 729','Aliyaview','45169',-14.3990,-120.7677) RETURNING address_id INTO address_id;
INSERT INTO companies(company_id, address_id,name,catchPhrase,bs) VALUES (NEXTVAL(companies_sequence),8,'Abernathy Group','Implemented secondary concept','e-enable extensible e-tailers') RETURNING company_id INTO company_id;
INSERT INTO users(user_id,company_id,name,username,email,phone,website) VALUES (8,8,'Nicholas Runolfsdottir V','Maxime_Nienow','Sherwood@rosamond.me','586.493.6943 x140','jacynthe.com') RETURNING user_id INTO user_id;
UPDATE users SET company_id = 8 WHERE user_id = 8;
========================================
INSERT INTO addresses(address_id,street,suite,city,zipcode,lat,lng) VALUES (NEXTVAL(addresses_sequence),'Dayna Park','Suite 449','Bartholomebury','76495-3109',24.6463,-168.8889) RETURNING address_id INTO address_id;
INSERT INTO companies(company_id, address_id,name,catchPhrase,bs) VALUES (NEXTVAL(companies_sequence),9,'Yost and Sons','Switchable contextually-based project','aggregate real-time technologies') RETURNING company_id INTO company_id;
INSERT INTO users(user_id,company_id,name,username,email,phone,website) VALUES (9,9,'Glenna Reichert','Delphine','Chaim_McDermott@dana.io','(775)976-6794 x41206','conrad.com') RETURNING user_id INTO user_id;
UPDATE users SET company_id = 9 WHERE user_id = 9;
========================================
INSERT INTO addresses(address_id,street,suite,city,zipcode,lat,lng) VALUES (NEXTVAL(addresses_sequence),'Kattie Turnpike','Suite 198','Lebsackbury','31428-2261',-38.2386,57.2232) RETURNING address_id INTO address_id;
INSERT INTO companies(company_id, address_id,name,catchPhrase,bs) VALUES (NEXTVAL(companies_sequence),10,'Hoeger LLC','Centralized empowering task-force','target end-to-end models') RETURNING company_id INTO company_id;
INSERT INTO users(user_id,company_id,name,username,email,phone,website) VALUES (10,10,'Clementina DuBuque','Moriah.Stanton','Rey.Padberg@karina.biz','024-648-3804','ambrose.net') RETURNING user_id INTO user_id;
UPDATE users SET company_id = 10 WHERE user_id = 10;
========================================